package cn.bitkit.tools.file;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;

import cn.bitkit.base.util.DateUtil;
import cn.bitkit.base.util.ReflectUtil;
import cn.bitkit.base.util.StringUtil;
import cn.bitkit.tools.annotation.ExcelColumn;
import cn.bitkit.tools.common.IOUtil;
import cn.bitkit.tools.data.BigDecimalUtil;
import cn.bitkit.tools.data.JSONUtil;
import lombok.*;
import lombok.experimental.SuperBuilder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 	行和列均从0开始计数
 * @author changbo
 *
 */
@Slf4j
public class ExcelHelper {

    @SneakyThrows
    public static ExcelReader getReader(String fileName){
        return getReader(new FileInputStream(fileName), fileName.endsWith(".xls") ? BaseExcel.XLS : BaseExcel.XLSX);
    }

    public static ExcelReader getReader(InputStream inputStream, int fileType){
        return ExcelReader.builder()
                .input(inputStream)
                .fileType(fileType)
                .build();
    }

    @SneakyThrows
    public static ExcelWriter getWriter(String fileName){
        return getWriter(new FileOutputStream(fileName), fileName.endsWith(".xls") ? BaseExcel.XLS : BaseExcel.XLSX);
    }

    public static ExcelWriter getWriter(OutputStream outputStream, int fileType){
        return ExcelWriter.builder()
                .output(outputStream)
                .fileType(fileType)
                .build();
    }

    @Getter
    @Setter
    @SuperBuilder
    private static class BaseExcel {
        public static final int XLS = 0;
        public static final int XLSX = 1;
        public static final int XLS_MAX_ROW_NUM = 65535;
        protected int fileType;
        protected int lineIndex;
        protected Workbook workbook;
        protected Sheet sheet;
    }

    @Getter
    @Setter
    @SuperBuilder
    public static class ExcelWriter extends BaseExcel {
        private int rowHeight;
        private Map<Integer, Integer> columWidth;
        private OutputStream output;

        public ExcelWriter open(String sheetName){
            if(fileType == XLS){
                workbook = new HSSFWorkbook();
            }else {
                workbook = new XSSFWorkbook();
            }
            sheet = workbook.createSheet(sheetName);
            //设置列宽
            if(columWidth != null){
                columWidth.forEach((index, width) -> sheet.setColumnWidth(index, width*256));
            }
            return this;
        }

        public <T> void writeHeader(Class<T> clazz) {
            List<Field> fieldList = ReflectUtil.getFields(clazz);
            fieldList.forEach(field -> {
                ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                if(excelColumn != null && excelColumn.width() > 0) {
                    columWidth.put(excelColumn.sort(), excelColumn.width()*256);
                }
            });
            Map<Integer, String> map = new HashMap<>(fieldList.size());
            for(Field field : fieldList) {
                ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                if(excelColumn != null) {
                    map.put(excelColumn.sort(), excelColumn.name());
                }
            }
            writeLine(map);
        }

        public <T> void writeList(List<T> list, Class<T> clazz) {
            List<Field> fieldList = ReflectUtil.getFields(clazz);
            for(T obj : list) {
                Map<Integer, String> map = new HashMap<>(fieldList.size());
                for(Field field : fieldList) {
                    Object val = ReflectUtil.getValue(obj, field.getName());
                    ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                    if(excelColumn != null) {
                        map.put(excelColumn.sort(), StringUtil.getValue(val));
                    }
                }
                writeLine(map);
            }
        }

        public void writeLine(String[] values){
            if(StringUtil.hasValue(values)){
                Map<Integer, String> map = new HashMap<>(values.length);
                for(int i = 0; i< values.length; i++){
                    map.put(i, StringUtil.getValue(values[i]));
                }
                writeLine(map);
            }
        }

        public void writeLine(List<String> list){
            if(list != null && !list.isEmpty()){
                Map<Integer, String> map = new HashMap<>(list.size());
                for(int i=0;i<list.size(); i++){
                    map.put(i, StringUtil.getValue(list.get(i)));
                }
                writeLine(map);
            }
        }

        public void writeLine(Map<Integer, String> map){
            if(fileType == XLS && lineIndex > XLS_MAX_ROW_NUM){
                return ;
            }
            if(StringUtil.hasValue(map.values().toArray())){
                Row row = sheet.createRow(lineIndex);
                row.setHeight((short)rowHeight);
                for(Integer i : map.keySet()){
                    if(StringUtil.hasValue(map.get(i))){
                        Cell cell = row.createCell(i);
                        cell.setCellValue(map.get(i));
                    }
                }
            }
            lineIndex++;
        }

        public void close(){
            IOUtil.close(workbook);
            IOUtil.flush(output);
            IOUtil.close(output);
        }
    }

    @Getter
    @Setter
    @SuperBuilder
    public static class ExcelReader extends BaseExcel{
        private InputStream input;
        private boolean ignoreBlankRow;

        public ExcelReader open(){
            return open(0);
        }

        @SneakyThrows
        public ExcelReader open(int sheetIndex){
            if(fileType == XLS){
                workbook = new HSSFWorkbook(input);
            }else {
                workbook = new XSSFWorkbook(input);
            }
            if(sheetIndex < workbook.getNumberOfSheets()){
                sheet = workbook.getSheetAt(sheetIndex);
            }
            return this;
        }

        public ExcelReader skip(int step){
            lineIndex += step;
            return this;
        }

        public Map<Integer, String> readLine(){
            if(sheet == null || lineIndex > sheet.getLastRowNum()){
                return null;
            }
            Map<Integer, String> map = getRows(lineIndex);
            lineIndex++;
            //如果数据为空,重新取数据
            while(ignoreBlankRow && isEmptyData(map)) {
                if(lineIndex > sheet.getLastRowNum()){
                    return null;
                }
                map = getRows(lineIndex);
                lineIndex++;
            }
            return map;
        }

        public List<Map<Integer, String>> readAll(){
            List<Map<Integer, String>> list = new ArrayList<>();
            Map<Integer, String> line;
            while((line = readLine()) != null){
                list.add(line);
            }
            close();
            return list;
        }

        public <T> List<T> readAll(Class<T> clazz){
            List<T> list = new ArrayList<>();
            List<Field> fieldList = ReflectUtil.getFields(clazz);
            List<Map<Integer, String>> mapList = readAll();
            for(Map<Integer, String> map : mapList) {
                Map<String, String> row = new HashMap<>(map.size());
                for(Field field : fieldList) {
                    ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
                    if(excelColumn == null) {
                        continue;
                    }
                    String value = map.get(excelColumn.sort());
                    row.put(field.getName(), value);
                }
                T obj = JSONUtil.toObject(JSONUtil.toJson(row), clazz);
                list.add(obj);
            }
            return list;
        }


        public Map<Integer, String> getRows(int index){
            if(fileType == XLS && index > XLS_MAX_ROW_NUM){
                return null;
            }
            Row row = sheet.getRow(index);
            if(row == null) {
                return null;
            }
            int wide = row.getLastCellNum();
            Map<Integer, String> map = new HashMap<>(wide);
            for(int j=0; j<wide; j++){
                Cell cell = row.getCell(j);
                map.put(j, getCellString(cell));
            }
            return map;
        }

        private String getCellString(Cell cell){
            if(cell == null){
                return "";
            }
            CellType type = cell.getCellType();
            if(type == CellType.BLANK){
                return "";
            }
            if(type == CellType._NONE){
                return StringUtil.toString(cell.getStringCellValue());
            }
            if(type == CellType.STRING){
                return StringUtil.toString(cell.getStringCellValue());
            }
            if(type == CellType.BOOLEAN){
                return StringUtil.toString(cell.getBooleanCellValue());
            }
            if(type == CellType.FORMULA){
                return StringUtil.toString(cell.getNumericCellValue());
            }
            if(type == CellType.NUMERIC){
                int format = cell.getCellStyle().getDataFormat();
                if(format > 0) {
                    return DateUtil.toDateTime(cell.getDateCellValue());
                }
                BigDecimal value = BigDecimal.valueOf(cell.getNumericCellValue());
                return StringUtil.toString(BigDecimalUtil.getView(value));
            }
            return "";
        }

        private boolean isEmptyData(Map<Integer, String> map){
            if(map == null || map.isEmpty()){
                return true;
            }
            return StringUtil.allIsEmpty(map.values().toArray());
        }

        public void close(){
            IOUtil.close(workbook);
            IOUtil.close(input);
        }

    }

}

